
----------------- DROP TABLE AND SEQUENCE -----------------
DROP TABLE MAMMAMEMBER;
DROP TABLE BABY;
DROP TABLE ADMINCHECK;
DROP TABLE BABYALLDAY;
DROP TABLE BABYINFO;
DROP TABLE BABYEAT;
DROP TABLE BABYPLAY;
DROP TABLE BABYSLEEP;
DROP TABLE FRIENDS;
DROP TABLE INFORMATION;
DROP TABLE CALENDAR;
DROP TABLE VACCINATION;
DROP TABLE PHOTO;
DROP TABLE PHOTOCOMMENT;
DROP TABLE SALEBOARD;
DROP TABLE SALEBOARDCOMMENT;
DROP TABLE BOARD;
DROP TABLE BOARDCOMMENT;
DROP TABLE UPDATES;

DROP SEQUENCE BABY_SEQ;
DROP SEQUENCE ADMINCHECK_SEQ;
DROP SEQUENCE BABYALLDAY_SEQ;
DROP SEQUENCE BABYINFO_SEQ;
DROP SEQUENCE BABYEAT_SEQ;
DROP SEQUENCE BABYPLAY_SEQ;
DROP SEQUENCE BABYSLEEP_SEQ;
DROP SEQUENCE INFORMATION_SEQ;
DROP SEQUENCE CALENDAR_SEQ;
DROP SEQUENCE VACCINATION_SEQ;
DROP SEQUENCE PHOTO_SEQ;
DROP SEQUENCE PHOTOCOMMENT_SEQ;
DROP SEQUENCE SALEBOARD_SEQ;
DROP SEQUENCE SALEBOARDCOMMENT_SEQ;
DROP SEQUENCE BOARD_SEQ;
DROP SEQUENCE SEQUENCE BOARDCOMMENT_SEQ;
DROP SEQUENCE UPDATES_SEQ;

----------------- member ------------------

CREATE TABLE MAMMAMEMBER
(
	EMAIL 			VARCHAR2(25) 						CONSTRAINT MM_EM_PK 	PRIMARY KEY 
	, NAME 			VARCHAR2(12) 						CONSTRAINT MM_NM_NN 	NOT NULL
	, NICKNAME 		VARCHAR2(20) 						CONSTRAINT MM_NI_NN 	NOT NULL
	, PASSWORD 		VARCHAR2(16) 						CONSTRAINT MM_PW_NN 	NOT NULL
	, HASBABY		CHAR(1)			DEFAULT 'N'			CONSTRAINT MM_HB_NN		NOT NULL
	, INPUTDATE		DATE			DEFAULT SYSDATE		CONSTRAINT MM_DT_NN		NOT NULL
	, HOMETOWN		VARCHAR2(20)						CONSTRAINT MM_HT_NN		NOT NULL
	, LASTACCESS	DATE			DEFAULT SYSDATE		CONSTRAINT MM_LA_NN		NOT NULL
	, MESSAGE		VARCHAR2(300)	DEFAULT ''
	, PROFILE_ID	NUMBER
);

---------------------
CREATE SEQUENCE BABY_SEQ; -- SEQUENCE FOR BABY TABLE

CREATE TABLE BABY
(
	BABYNUM			NUMBER										CONSTRAINT BB_BN_PK		PRIMARY KEY
	, NAME			VARCHAR2(18)								CONSTRAINT BB_NM_NN		NOT NULL
	, SEX			CHAR(4)			DEFAULT '여'				CONSTRAINT BB_GD_NN		NOT NULL
	, BORNDATE		DATE			DEFAULT SYSDATE				CONSTRAINT BB_BD_NN		NOT NULL
	, BORNWEEK		VARCHAR2(10)	DEFAULT '10주'				CONSTRAINT BB_BW_NN		NOT NULL
	, DELIVERYINFO	VARCHAR2(30)	DEFAULT '자연분만'			CONSTRAINT BB_DI_NN		NOT NULL
	, BIRTHTYPE		VARCHAR2(30)	DEFAULT '만삭'				CONSTRAINT BB_BT_NN		NOT NULL
	, BIRTHLOCATION	VARCHAR2(25)								CONSTRAINT BB_BL_NN		NOT NULL
	, MEMBEREMAIL	VARCHAR2(25)								CONSTRAINT BB_ME_NN		NOT NULL
	, CONSTRAINT BB_ME_FK	FOREIGN KEY (MEMBEREMAIL)	REFERENCES MAMMAMEMBER (EMAIL) ON DELETE CASCADE
);

insert into baby ( babynum, name, borndate, birthlocation, memberemail ) 
values( BABY_SEQ.nextval, '셋째', '2014-10-23', '경기', 'hjk1019@gmail.com');

insert into babyInfo(INFONUM, HEIGHT, WEIGHT, HEADSIZE, INPUTDATE, BABYNUM)
values(babyInfo_seq.nextval, 13, 3, 14, '2014-10-25', 1);

----------------- admin ------------------
CREATE SEQUENCE ADMINCHECK_SEQ;

CREATE TABLE ADMINCHECK
(
	ADMINCHECK 		NUMBER											CONSTRAINT AC_AC_PK		PRIMARY KEY
	, EMAIL			VARCHAR2(25)									CONSTRAINT AC_EM_NN		NOT NULL
	, CONSTRAINT AC_AC_FK	FOREIGN KEY (EMAIL)	REFERENCES MAMMAMEMBER ( EMAIL ) ON DELETE CASCADE
);

----------------- baby book ------------------

CREATE TABLE BABYALLDAY
(
	BABYDAYID		NUMBER											CONSTRAINT BA_ID_PK		PRIMARY KEY
	, INPUTDATE		DATE			DEFAULT SYSDATE					CONSTRAINT BA_DT_NN		NOT NULL
	, STARTDATE		DATE
	, ENDDATE		DATE
	, BABYNUM		NUMBER											CONSTRAINT BA_BN_NN		NOT NULL
	, CONSTRAINT BA_BN_FK	FOREIGN KEY (BABYNUM)	REFERENCES BABY ( BABYNUM ) ON DELETE CASCADE
);

CREATE SEQUENCE BABYALLDAY_SEQ; -- SEQUENCE FOR BABY_ALL_DAY

-----------------------
CREATE TABLE BABYINFO
(
	INFONUM			NUMBER										CONSTRAINT BI_IN_PK		PRIMARY KEY
	, HEIGHT		NUMBER(10, 4)								CONSTRAINT BI_HT_NN		NOT NULL
	, WEIGHT		NUMBER(10, 4)								CONSTRAINT BI_WT_NN		NOT NULL
	, HEADSIZE		NUMBER(10, 4)								CONSTRAINT BI_HS_NN		NOT NULL
	, INPUTDATE		DATE										CONSTRAINT BI_DT_NN		NOT NULL
	, BABYNUM		NUMBER										CONSTRAINT BI_BN_NN		NOT NULL
	, CONSTRAINT BI_BN_FK	FOREIGN KEY (BABYNUM)	REFERENCES BABY ( BABYNUM ) ON DELETE CASCADE
);

CREATE SEQUENCE BABYINFO_SEQ; -- SEQUENCE FOR BABYINFO TABLE

---------------------
CREATE TABLE BABYEAT
(
	ID				NUMBER											CONSTRAINT BE_ID_PK		PRIMARY KEY
	, CONTENT		VARCHAR2(3000)
	, VOLUME		NUMBER
	, EXCREMENT		VARCHAR2(20)	DEFAULT 'NULL'
	, URINE			VARCHAR2(20)	DEFAULT 'NULL'
	, BABYDAYID		NUMBER											CONSTRAINT BE_BI_NN		NOT NULL
	, CONSTRAINT BE_BI_FK	FOREIGN KEY (BABYDAYID)	REFERENCES BABYALLDAY ( BABYDAYID ) ON DELETE CASCADE
);

CREATE SEQUENCE BABYEAT_SEQ; -- SEQUENCE FOR BABY_EAT

---------------------
CREATE TABLE BABYPLAY
(
	ID				NUMBER											CONSTRAINT BP_ID_PK		PRIMARY KEY
	, PLAY			VARCHAR2(600)	DEFAULT 'NULL'
	, BABYDAYID		NUMBER											CONSTRAINT BP_BI_NN		NOT NULL
	, CONSTRAINT BP_BI_FK	FOREIGN KEY (BABYDAYID)	REFERENCES BABYALLDAY ( BABYDAYID ) ON DELETE CASCADE
);

CREATE SEQUENCE BABYPLAY_SEQ; -- SEQUENCE FOR BABY_PLAY

---------------------
CREATE TABLE BABYSLEEP
(
	ID				NUMBER											CONSTRAINT BS_ID_PK		PRIMARY KEY
	, VOLUME		NUMBER
	, DEGREES		VARCHAR2(1000)
	, BABYDAYID		NUMBER											CONSTRAINT BS_BI_NN		NOT NULL
	, CONSTRAINT BS_BI_FK	FOREIGN KEY (BABYDAYID)	REFERENCES BABYALLDAY ( BABYDAYID ) ON DELETE CASCADE
);

CREATE SEQUENCE BABYSLEEP_SEQ;


----------------- friends ------------------

CREATE TABLE FRIENDS
(
	FRIEND_ID		NUMBER											CONSTRAINT FR_ID_PK		PRIMARY KEY
	, USER01		VARCHAR2(25)
	, USER02		VARCHAR2(25)
	-- 0: Pending Friend Request
	-- 1: Confirm Friend Request
	-- 2: You
	, STATUS		CHAR(1)			DEFAULT '0'						CONSTRAINT FR_ST_NN		NOT NULL
	, INPUTDATE		DATE			DEFAULT SYSDATE 				CONSTRAINT FR_DT_NN		NOT NULL
	, CONSTRAINT FR_U1_FK	FOREIGN KEY (USER01)	REFERENCES MAMMAMEMBER ( EMAIL ) ON DELETE CASCADE
	, CONSTRAINT FR_U2_FK	FOREIGN KEY (USER02)	REFERENCES MAMMAMEMBER ( EMAIL ) ON DELETE CASCADE
);

CREATE SEQUENCE FRIENDS_SEQ;


----------------- schedule ------------------

CREATE TABLE INFORMATION
(
	ID				NUMBER											CONSTRAINT IF_ID_PK		PRIMARY KEY
	, INFONAME		VARCHAR2(100)									CONSTRAINT IF_NM_NN		NOT NULL
	, INFOCONTENT	VARCHAR2(3000)									CONSTRAINT IF_CT_NN		NOT NULL
	, STARTDATE		DATE											CONSTRAINT IF_SD_NN		NOT NULL
	, ENDDATE		DATE
	, IMPORTANCE	NUMBER			DEFAULT 1
	, ADMINID		VARCHAR2(25)									CONSTRAINT IF_AI_NN		NOT NULL
);

CREATE SEQUENCE INFORMATION_SEQ; -- SEQUENCE FOR INFORMATION TABLE

---------------------
CREATE TABLE CALENDAR
(
	ID				NUMBER											CONSTRAINT CL_ID_PK		PRIMARY KEY
	, INFONAME		VARCHAR2(300)									CONSTRAINT CL_IN_NN		NOT NULL
	, INFOCONTENT	VARCHAR2(3000)									CONSTRAINT CL_IC_NN		NOT NULL
	, STARTDATE		DATE											CONSTRAINT CL_SD_NN		NOT NULL
	, ENDDATE		DATE
	, EMAIL			VARCHAR2(25)									CONSTRAINT CL_EM_NN		NOT NULL
	, CONSTRAINT CL_EM_FK	FOREIGN KEY (EMAIL)	REFERENCES MAMMAMEMBER (EMAIL) ON DELETE CASCADE
);

CREATE SEQUENCE CALENDAR_SEQ; -- SEQUENCE FOR CALENDAR TABLE

---------------------
CREATE TABLE VACCINATION
(
	ID				NUMBER											CONSTRAINT VC_ID_PK		PRIMARY KEY
	, TITLE			VARCHAR2(300)									CONSTRAINT VC_TT_NN		NOT NULL
	, CONTENT		VARCHAR2(1000)									CONSTRAINT VC_CT_NN		NOT NULL
	, STARTDATE		DATE											CONSTRAINT VC_SD_NN		NOT NULL
	, ENDMONTH		NUMBER											CONSTRAINT VC_ED_NN		NOT NULL
);

CREATE SEQUENCE VACCINATION_SEQ; -- SEQUENCE FOR VACCINATION TABLE


----------------- photo ------------------

CREATE TABLE PHOTO
(
	ID				NUMBER											CONSTRAINT PT_ID_PK		PRIMARY KEY
	, CONTENT		VARCHAR2(1000)
	, NICKNAME		VARCHAR2(20)									CONSTRAINT PT_NI_NN		NOT NULL
	, INPUTDATE		DATE			DEFAULT SYSDATE 				CONSTRAINT PT_DT_NN	 	NOT NULL
	-- 사진의 종류
	, KIND			VARCHAR2(100)	DEFAULT 'default'				CONSTRAINT PT_KN_NN		NOT NULL
	, LIKEIT		NUMBER			DEFAULT 0						CONSTRAINT PT_LI_NN		NOT NULL
	, SAVEDFILE		VARCHAR2(100)
	, ORIGINALFILE	VARCHAR2(200)
	, EMAIL			VARCHAR2(25)									CONSTRAINT PT_EM_NN		NOT NULL
	, CONSTRAINT PT_EM_FK	FOREIGN KEY (EMAIL)	REFERENCES MAMMAMEMBER ( EMAIL ) ON DELETE CASCADE
);

CREATE SEQUENCE PHOTO_SEQ; -- SEQUENCE FOR PHOTO TABLE

---------------------
CREATE TABLE PHOTOCOMMENT
(
	PHOTOCOMMENTNUM		NUMBER												CONSTRAINT PC_NM_PK	PRIMARY KEY
	, CONTENT			VARCHAR2(1000)										CONSTRAINT PC_CT_NN NOT NULL
	, NICKNAME			VARCHAR2(20)										CONSTRAINT PC_NI_NN NOT NULL
	, INPUTDATE			DATE			DEFAULT SYSDATE						CONSTRAINT PC_DT_NN NOT NULL
	, LIKEIT			NUMBER			DEFAULT 0							CONSTRAINT PC_LI_NN	NOT NULL
	, ID				NUMBER												CONSTRAINT PC_ID_NN NOT NULL
	, EMAIL				VARCHAR2(25)										CONSTRAINT PC_EM_NN NOT NULL
	, CONSTRAINT PC_EM_FK	FOREIGN KEY (EMAIL)	REFERENCES MAMMAMEMBER ( EMAIL ) ON DELETE CASCADE
	, CONSTRAINT PC_ID_FK	FOREIGN KEY (ID)	REFERENCES PHOTO ( ID ) 		 ON DELETE CASCADE
);

CREATE SEQUENCE PHOTOCOMMENT_SEQ; -- SEQUENCE FOR PHOTO_COMMENT TABLE


----------------- sale ------------------

CREATE TABLE SALEBOARD
(
	SALEBOARDNUM		NUMBER												CONSTRAINT SB_SN_PK PRIMARY KEY
	, STATE				VARCHAR2(20)										CONSTRAINT SB_ST_NN NOT NULL
	, TITLE				VARCHAR2(300)										CONSTRAINT SB_TT_NN NOT NULL
	, NICKNAME			VARCHAR2(20)										CONSTRAINT SB_NI_NN NOT NULL
	, CONTENT			VARCHAR2(3000)
	, INPUTDATE			DATE			DEFAULT SYSDATE						CONSTRAINT SB_DT_NN NOT NULL
	, EMAIL				VARCHAR2(25)	DE									CONSTRAINT SB_EM_NN NOT NULL,
	, CONSTRAINT SB_EM_FK	FOREIGN KEY (EMAIL)	REFERENCES MAMMAMEMBER ( EMAIL ) ON DELETE CASCADE
);

CREATE SEQUENCE SALEBOARD_SEQ; -- SEQUENCE FOR SALEBOARD TABLE

---------------------
CREATE TABLE SALEBOARDCOMMENT
(
	SALECOMMENTNUM		NUMBER													CONSTRAINT SC_CN_NN NOT NULL
	, NICKNAME			VARCHAR2(20)											CONSTRAINT SC_NI_NN NOT NULL
	, CONTENT			VARCHAR2(1000)											CONSTRAINT SC_CT_NN NOT NULLL
	, INPUTDATE			DATE			DEFAULT SYSDATE							CONSTRAINT SC_DT_NN NOT NULL
	, EMAIL				VARCHAR2(25)											CONSTRAINT SC_EM_NN NOT NULL
	, SALEBOARDNUM		NUMBER													CONSTRAINT SC_SN_NN NOT NULL,
	, CONSTRAINT SC_EM_FK	FOREIGN KEY (EMAIL)	REFERENCES MAMMAMEMBER ( EMAIL ) ON DELETE CASCADE
	, CONSTRAINT SC_SN_FK	FOREIGN KEY (SALEBOARDNUM)	REFERENCES SALEBOARD ( SALEBOARDNUM ) ON DELETE CASCADE
);

CREATE SEQUENCE SALEBOARDCOMMENT_SEQ; -- SEQUENCE FOR SALEBOARD TABLE

----------------- board ------------------

CREATE TABLE BOARD
(
	BOARDNUM			NUMBER											CONSTRAINT BD_BN_PK PRIMARY KEY
	, STATE				VARCHAR2(20)									CONSTRAINT BD_ST_NN NOT NULL
	, TITLE				VARCHAR2(300)									CONSTRAINT BD_TT_NN NOT NULL
	, NICKNAME			VARCHAR2(20)									CONSTRAINT BD_NI_NN NOT NULL
	, CONTENT			VARCHAR2(3000)
	, INPUTDATE			DATE			DEFAULT SYSDATE					CONSTRAINT BD_DT_NN NOT NULL
	, EMAIL				VARCHAR2(25)									CONSTRAINT BD_EM_NN NOT NULL
);

CREATE SEQUENCE BOARD_SEQ; -- SEQUENCE FOR BOARD TABLE

---------------------
CREATE TABLE BOARDCOMMENT
(
	COMMENTNUM			NUMBER											 	CONSTRAINT BC_CN_PK PRIMARY KEY
	, NICKNAME			VARCHAR2(20)										CONSTRAINT BC_NI_NN NOT NULL
	, CONTENT			VARCHAR2(3000)										CONSTRAINT BC_CT_NN NOT NULL
	, INPUTDATE			DATE			DEFAULT SYSDATE						CONSTRAINT BC_DT_NN NOT NULL
	, EMAIL				VARCHAR2(25)										CONSTRAINT BC_EM_NN NOT NULL
	, BOARDNUM			NUMBER												CONSTRAINT BC_BN_NN NOT NULL
	, CONSTRAINT BC_EM_FK	FOREIGN KEY (EMAIL)	REFERENCES MAMMAMEMBER ( EMAIL ) ON DELETE CASCADE
	, CONSTRAINT BC_BN_FK	FOREIGN KEY (BOARDNUM)	REFERENCES BOARD ( COMMENTNUM ) ON DELETE CASCADE
);

CREATE SEQUENCE BOARDCOMMENT_SEQ; -- SEQUENCE FOR BOARDCOMMENT TABLE


---------------------
CREATE TABLE UPDATES
(
	ID					NUMBER												CONSTRAINT UD_ID_PK PRIMARY KEY
	, CONTENT			VARCHAR2(1000)										CONSTRAINT UD_CT_NN NOT NULL
	, INPUTDATE			DATE			DEFAULT SYSDATE						CONSTRAINT UD_DT_NN NOT NULL
	-- 0: not checked
	-- 1: checked
	, STATUS			CHAR(1)			DEFAULT '0' 						CONSTRAINT UD_ST_NN NOT NULL
	, EMAIL				VARCHAR2(25)										CONSTRAINT UD_EM_NN NOT NULL
	, CONSTRAINT UD_EM_FK	FOREIGN KEY (EMAIL)	REFERENCES MAMMAMEMBER ( EMAIL ) ON DELETE CASCADE
);

CREATE SEQUENCE UPDATES_SEQ;
